﻿using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace _2308NA.DAL
{
    public class SQLHelp
    {
        //增删改 查

        //数据库链接地址
        string connstr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;


        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, SqlParameter[] parameters)
        {

            //在操作完之后销毁 对象
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                //参数化
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                conn.Open();
                int num = cmd.ExecuteNonQuery();
                conn.Close();

                return num;
            }

        }


        /// <summary>
        /// 单值查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, SqlParameter[] parameters)
        {
            //在操作完之后销毁 对象
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                //参数化
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();

                return obj;
            }
        }


        /// <summary>
        /// 适配器查询表格
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, SqlParameter[] parameters)
        {
            //在操作完之后销毁 对象
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                DataTable dt = new DataTable();

                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                if (parameters != null)
                    sda.SelectCommand.Parameters.AddRange(parameters);

                sda.Fill(dt);

                return dt;
            }
        }


        /// <summary>
        /// 读取器读取数据
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, SqlParameter[] parameters)
        {
            //在操作完之后销毁 对象
            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            conn.Open();
            //CommandBehavior.CloseConnection  在关闭读取器的同时关闭 conn
            SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            return sdr;

        }
    }
}
